﻿/**************************************************************
 *
 * 唯一标识：1459a54c-bc46-4589-8154-7e64f50889cd
 * 命名空间：Sgr.Generator.Segment.Dal.Impl
 * 创建时间：2024/6/17 15:08:26
 * 机器名称：DESKTOP-HJ4OAG9
 * 创建者：CocoYuan
 * 电子邮箱：fengqinhua2016@163.com
 * 描述：
 *
 **************************************************************/

using Microsoft.Extensions.Options;
using Sgr.Data;
using Sgr.Data.DatabaseTranslators;
using Sgr.Exceptions;
using Sgr.Generator.Segment.Entities;
using System.Data;
using System.Data.Common;

namespace Sgr.Generator.Segment.Storage
{
    /// <summary>
    /// 号段实体数据存储实现类
    /// </summary>
    public class SegmentEntityDataStorage : ISegmentEntityDataStorage
    {
        private const string TableName = "gen_id_generator";
        private const int MaxKeyLength = 128;

        private readonly string _dataConnectionString;
        private readonly IDatabaseTranslator _translator;
        private readonly SegmentNumIdGeneratorOptions _options;

        public SegmentEntityDataStorage(
            IDataConnectionStringManager dataConnectionStringManager,
            IDatabaseTranslatorFactory databaseTranslatorFactory,
            IOptions<SegmentNumIdGeneratorOptions> option)
        {
            _options = option.Value;
            DataConnectionString dataConnection = dataConnectionStringManager.GetDataConnectionString(_options.DatabaseName ?? "");
            _dataConnectionString = dataConnection.ConnectionString;
            _translator = databaseTranslatorFactory.CreateTranslator(dataConnection.DbProvider);
        }

        private DbConnection GetDbConnection()
        {
            DbConnection dbConnection = _translator.CreateConnection() ?? throw new BusinessException("创建 DbConnection 失败!");
            dbConnection.ConnectionString = _dataConnectionString;
            return dbConnection;
        }

        public virtual async Task CreateTableIfNotExist(CancellationToken cancellationToken = default)
        {
            await using var connection = GetDbConnection();
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            await using var command = connection.CreateCommand();
            command.CommandText = GetCreateTableSql();
            await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
        }

        /// <summary>
        /// 插入新的号段记录（若已存在则按数据库差异可能忽略）。
        /// 返回值语义：true=成功执行（插入或已存在），false=未插入但被显式忽略（主要是 PostgreSQL/SQLite 的 DO NOTHING / IGNORE 行为）。
        /// </summary>
        public virtual async Task<bool> InsertAsync(string key, int s_step, string s_description, CancellationToken cancellationToken = default)
        {
            if (string.IsNullOrEmpty(key))
                throw new ArgumentException("标识键不能为空", nameof(key));

            if (key.Length > MaxKeyLength)
                throw new ArgumentException($"标识键长度不能超过 {MaxKeyLength} 字符: {key.Length}", nameof(key));

            if (s_step <= 0)
                throw new ArgumentException("步长必须大于0", nameof(s_step));

            if (s_step < _options.MinStepLowerBound)
                throw new BusinessException($"步长低于限制: {s_step} < {_options.MinStepLowerBound}");

            if (s_step > _options.MaxStepUpperBound)
                throw new BusinessException($"步长超出限制: {s_step} > {_options.MaxStepUpperBound}");

            string sql = _translator.DataBaseType switch
            {
                DatabaseType.MySql => $@"
INSERT INTO {TableName} (id, max_id, step, description)
VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")})
ON DUPLICATE KEY UPDATE id = id;",
                DatabaseType.SqlServer => $@"
IF NOT EXISTS (SELECT 1 FROM {TableName} WHERE id = {_translator.QuoteParameter("id")})
INSERT INTO {TableName} (id, max_id, step, description)
VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")});",
                DatabaseType.SQLite => $@"
INSERT OR IGNORE INTO {TableName} (id, max_id, step, description)
VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")});",
                DatabaseType.Oracle => $@"
MERGE INTO {TableName} target
USING (SELECT {_translator.QuoteParameter("id")} AS id FROM dual) source
ON (target.id = source.id)
WHEN NOT MATCHED THEN
  INSERT (id, max_id, step, description)
  VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")})",
                DatabaseType.PostgreSql => $@"
INSERT INTO {TableName} (id, max_id, step, description)
VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")})
ON CONFLICT (id) DO NOTHING;",
                DatabaseType.DB2 => $@"
MERGE INTO {TableName} AS target
USING (VALUES ({_translator.QuoteParameter("id")})) AS source(id)
ON target.id = source.id
WHEN NOT MATCHED THEN
  INSERT (id, max_id, step, description)
  VALUES ({_translator.QuoteParameter("id")}, {_translator.QuoteParameter("max_id")}, {_translator.QuoteParameter("step")}, {_translator.QuoteParameter("description")})",
                _ => throw new BusinessException($"不支持的数据库类型：{_translator.DataBaseType}")
            };

            await using var connection = GetDbConnection();
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            await using var command = connection.CreateCommand();
            command.CommandText = sql;

            AddParameter(command, _translator.QuoteParameter("id"), key);
#if DEBUG
            AddParameter(command, _translator.QuoteParameter("max_id"), 102400);
#else
            AddParameter(command, _translator.QuoteParameter("max_id"), 1024000);
#endif
            AddParameter(command, _translator.QuoteParameter("step"), s_step);
            AddParameter(command, _translator.QuoteParameter("description"), s_description ?? (object)DBNull.Value);

            var affected = await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);

            // 统一语义：PostgreSQL / SQLite 插入已存在时 affected=0，也视为成功（返回 true）
            return affected > 0 || _translator.DataBaseType is DatabaseType.PostgreSql or DatabaseType.SQLite;
        }

        /// <summary>
        /// 获取所有号段实体记录
        /// </summary>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>号段实体集合</returns>
        public virtual async Task<IEnumerable<SegmentEntity>> GetAllAsync(CancellationToken cancellationToken = default)
        {
            var list = new List<SegmentEntity>();
            await using var connection = GetDbConnection();
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            await using var command = connection.CreateCommand();
            command.CommandText = $"SELECT id, max_id, step, description FROM {TableName}";
            await using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                list.Add(MapSegmentEntity(reader));
            }
            return list;
        }

        /// <summary>
        /// 获取所有号段标识
        /// </summary>
        /// <param name="cancellationToken">取消令牌</param>
        /// <returns>标识字符串集合</returns>
        public virtual async Task<IEnumerable<string>> GetIdsAsync(CancellationToken cancellationToken = default)
        {
            var list = new List<string>();
            await using var connection = GetDbConnection();
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            await using var command = connection.CreateCommand();
            command.CommandText = $"SELECT id FROM {TableName}";
            await using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                list.Add(reader.GetString(0));
            }
            return list;
        }

        public Task<SegmentEntity> UpdateMaxIdAndGetGenSegmentAsync(string key, CancellationToken cancellationToken = default)
            => InternalUpdateAndFetchAsync(key, null, cancellationToken);

        public Task<SegmentEntity> UpdateMaxIdAndGetGenSegmentAsync(string key, int step, CancellationToken cancellationToken = default)
            => InternalUpdateAndFetchAsync(key, step, cancellationToken);

        private async Task<SegmentEntity> InternalUpdateAndFetchAsync(string key, int? overrideStep, CancellationToken cancellationToken)
        {
            if (string.IsNullOrWhiteSpace(key))
                throw new ArgumentException("标识键不能为空", nameof(key));

            if (overrideStep.HasValue)
            {
                if (overrideStep.Value <= 0)
                    throw new ArgumentException("步长必须大于0", nameof(overrideStep));

                if (overrideStep.Value < _options.MinStepLowerBound)
                    throw new BusinessException($"步长低于限制: {overrideStep.Value} < {_options.MinStepLowerBound}");

                if (overrideStep.Value > _options.MaxStepUpperBound)
                    throw new BusinessException($"步长超出限制: {overrideStep.Value} > {_options.MaxStepUpperBound}");
            }

            await using var connection = GetDbConnection();
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            await using var transaction = await connection.BeginTransactionAsync(IsolationLevel.ReadCommitted, cancellationToken).ConfigureAwait(false);

            try
            {
                await using var command = connection.CreateCommand();
                command.Transaction = transaction;
                command.CommandText = BuildUpdateFetchSql(overrideStep.HasValue);

                AddParameter(command, _translator.QuoteParameter("id"), key);
                if (overrideStep.HasValue)
                    AddParameter(command, _translator.QuoteParameter("step"), overrideStep.Value);

                SegmentEntity entity;

                switch (_translator.DataBaseType)
                {
                    case DatabaseType.PostgreSql:
                    case DatabaseType.SQLite:
                    case DatabaseType.SqlServer: // SqlServer 通过 OUTPUT 也走单语句
                        await using (var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
                        {
                            if (!await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
                                throw new BusinessException($"未找到指定号段: {key}");
                            entity = MapSegmentEntity(reader);
                        }
                        break;

                    default:
                        // 两段式：UPDATE + SELECT
                        var updateAffected = await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
                        if (updateAffected == 0)
                            throw new BusinessException($"未找到指定号段: {key}");

                        // 查询
                        await using (var fetch = connection.CreateCommand())
                        {
                            fetch.Transaction = transaction;
                            fetch.CommandText = $"SELECT id, max_id, step, description FROM {TableName} WHERE id = {_translator.QuoteParameter("s_id")}";
                            // 复用参数
                            AddParameter(fetch, _translator.QuoteParameter("s_id"), key);

                            await using var reader2 = await fetch.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
                            if (!await reader2.ReadAsync(cancellationToken).ConfigureAwait(false))
                                throw new BusinessException($"未找到指定号段: {key}");
                            entity = MapSegmentEntity(reader2);
                        }
                        break;
                }

                await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);

                return entity;
            }
            catch (BusinessException)
            {
                await transaction.RollbackAsync(cancellationToken).ConfigureAwait(false);
                throw; // 保留业务异常语义
            }
            catch (DbException) // 让上层可根据具体数据库异常类型（死锁、超时等）做策略处理
            {
                await transaction.RollbackAsync(cancellationToken).ConfigureAwait(false);
                throw; // 不再统一包装，避免语义丢失
            }
            catch (Exception ex)
            {
                await transaction.RollbackAsync(cancellationToken).ConfigureAwait(false);
                throw new BusinessException($"更新号段失败: Key={key}, Step={(overrideStep.HasValue ? overrideStep.Value.ToString() : "默认")}", ex);
            }
        }

        private string BuildUpdateFetchSql(bool hasOverrideStep)
        {
            string stepExpr = hasOverrideStep
                ? $"max_id = max_id + {_translator.QuoteParameter("step")}"
                : "max_id = max_id + step";

            return _translator.DataBaseType switch
            {
                DatabaseType.PostgreSql => $"UPDATE {TableName} SET {stepExpr} WHERE id = {_translator.QuoteParameter("id")} RETURNING id, max_id, step, description;",
                DatabaseType.SQLite => $"UPDATE {TableName} SET {stepExpr} WHERE id = {_translator.QuoteParameter("id")} RETURNING id, max_id, step, description;",
                DatabaseType.SqlServer => // 使用 OUTPUT 一次返回
                    $"UPDATE {TableName} SET {stepExpr} OUTPUT inserted.id, inserted.max_id, inserted.step, inserted.description WHERE id = {_translator.QuoteParameter("id")};",
                _ => $"UPDATE {TableName} SET {stepExpr} WHERE id = {_translator.QuoteParameter("id")};"
            };
        }

        private static void AddParameter(DbCommand command, string parameterName, object value)
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.Value = value ?? DBNull.Value;
            command.Parameters.Add(parameter);
        }

        private static SegmentEntity MapSegmentEntity(DbDataReader reader)
        {
            return new SegmentEntity
            {
                Id = reader.GetString(0),
                MaxId = reader.GetInt64(1),
                Step = reader.GetInt32(2),
                Description = reader.IsDBNull(3) ? "" : reader.GetString(3)
            };
        }

        /// <summary>
        /// 获取创建表的SQL语句
        /// </summary>
        /// <returns>根据不同数据库类型返回对应的建表SQL语句</returns>
        /// <exception cref="BusinessException">当数据库类型不支持时抛出异常</exception>
        private string GetCreateTableSql()
        {
            return _translator.DataBaseType switch
            {
                DatabaseType.MySql => @$"CREATE TABLE IF NOT EXISTS `{TableName}` (
  `id` varchar(128) NOT NULL DEFAULT '',
  `max_id` bigint(20) NOT NULL DEFAULT '1',
  `step` int(11) NOT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY(`id`)
);",
                DatabaseType.SqlServer => @$"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{TableName}]') AND type = N'U')
BEGIN
  CREATE TABLE [dbo].[{TableName}] (
    [id] varchar(128) NOT NULL DEFAULT '',
    [max_id] bigint NOT NULL DEFAULT 1,
    [step] int NOT NULL,
    [description] varchar(256) NULL,
    CONSTRAINT [PK_{TableName}] PRIMARY KEY ([id])
  )
END",
                DatabaseType.SQLite => @$"CREATE TABLE IF NOT EXISTS {TableName} (
  id varchar(128) NOT NULL DEFAULT '',
  max_id INTEGER NOT NULL DEFAULT 1,
  step INTEGER NOT NULL,
  description varchar(256) DEFAULT NULL,
  PRIMARY KEY(id)
);",
                DatabaseType.Oracle => @$"BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE {TableName} (
    id varchar2(128) DEFAULT '''' NOT NULL,
    max_id number(19) DEFAULT 1 NOT NULL,
    step number(10) NOT NULL,
    description varchar2(256) DEFAULT NULL,
    CONSTRAINT pk_{TableName} PRIMARY KEY(id)
  )';
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -955 THEN NULL; ELSE RAISE; END IF;
END;",
                DatabaseType.PostgreSql => @$"CREATE TABLE IF NOT EXISTS {TableName} (
  id varchar(128) NOT NULL DEFAULT '',
  max_id bigint NOT NULL DEFAULT 1,
  step integer NOT NULL,
  description varchar(256) DEFAULT NULL,
  CONSTRAINT pk_{TableName} PRIMARY KEY(id)
);",
                DatabaseType.DB2 => @$"BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' BEGIN END;
  EXECUTE IMMEDIATE 'CREATE TABLE {TableName} (
    id varchar(128) NOT NULL DEFAULT '''',
    max_id bigint NOT NULL DEFAULT 1,
    step integer NOT NULL,
    description varchar(256) DEFAULT NULL,
    CONSTRAINT pk_{TableName} PRIMARY KEY(id)
  )';
END",
                _ => throw new BusinessException($"不支持的数据库类型：{_translator.DataBaseType}")
            };
        }
    }
}